package com.demo.main.servlet;

import com.demo.main.utils.ServletUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import static com.demo.main.utils.ServletUtil.PERMISSION_PATH_ADMIN;

// 处理借阅记录相关请求的Servlet类。
@WebServlet({PERMISSION_PATH_ADMIN + "/data_analysis_condition"})

public class DataServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    // 数据库连接信息
    private static final String DB_URL = "jdbc:mysql://localhost:3306/lms_javaweb_b30";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "zwz050418";

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        ServletUtil servletUtil = new ServletUtil(request, response);
        // 设置响应内容类型为JSON
        response.setContentType("application/json;charset=UTF-8");
        // 初始化统计变量
        int todayBorrowCount = 0;
        int todayReturnCount = 0;
        int userCount = 0;
        int announcementCount = 0;
        String category_name = null;
        int borrow_count = 0;

        Date date1 = null, date2 = null, date3 = null, date4 = null, date5 = null, date6 = null, date7 = null;
        int books1 = 0, books2 = 0, books3 = 0, books4 = 0, books5 = 0, books6 = 0, books7 = 0;

        SimpleDateFormat dateFormat;
        if (servletUtil.getServletPath().equals((PERMISSION_PATH_ADMIN + "/data_analysis_condition"))) {// 创建数据库连接
            try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
                // 准备SQL查询语句，获取今日借书总数
                String borrowSql = "SELECT COUNT(*) AS total FROM borrow_record WHERE DATE(borrow_date) = CURDATE()";
                try (PreparedStatement borrowStmt = conn.prepareStatement(borrowSql);
                     ResultSet borrowRs = borrowStmt.executeQuery()) {
                    // 执行查询并获取结果
                    if (borrowRs.next()) {
                        todayBorrowCount = borrowRs.getInt("total");
                    }
                }
                // 准备SQL查询语句，获取今日归还总数
                String returnSql = "SELECT COUNT(*) AS total FROM borrow_record WHERE DATE(return_date) = CURDATE()";
                try (PreparedStatement returnStmt = conn.prepareStatement(returnSql);
                     ResultSet returnRs = returnStmt.executeQuery()) {
                    // 执行查询并获取结果
                    if (returnRs.next()) {
                        todayReturnCount = returnRs.getInt("total");
                        System.out.println("todayReturnCount = " + todayReturnCount);
                    }
                }
                // 准备SQL查询语句，获取用户总数
                String userSql = "SELECT COUNT(*) AS total FROM user";
                try (PreparedStatement userStmt = conn.prepareStatement(userSql);
                     ResultSet userRs = userStmt.executeQuery()) {
                    // 执行查询并获取结果
                    if (userRs.next()) {
                        userCount = userRs.getInt("total");
                        System.out.println("userCount = " + userCount);
                    }
                }
                // 准备SQL查询语句，获取公告总数
                String announcementSql = "SELECT COUNT(*) AS total FROM notice";
                try (PreparedStatement announcementStmt = conn.prepareStatement(announcementSql);
                     ResultSet announcementRs = announcementStmt.executeQuery()) {
                    // 执行查询并获取结果
                    if (announcementRs.next()) {
                        announcementCount = announcementRs.getInt("total");
                        System.out.println("announcementCount = " + announcementCount);
                    }
                }

                String maxSql = "SELECT bc.category_name, COUNT(*) AS borrow_count FROM borrow_record br JOIN book b ON br.book_id = b.book_id JOIN book_category bc ON b.category_id = bc.category_id WHERE DATE(br.borrow_date) = CURDATE() GROUP BY bc.category_id, bc.category_name ORDER BY borrow_count DESC LIMIT 1;";
                try (PreparedStatement maxStmt = conn.prepareStatement(maxSql);
                     ResultSet maxRs = maxStmt.executeQuery()) {
                    // 执行查询并获取结果
                    if (maxRs.next()) {
                        category_name = maxRs.getString("category_name");
                        borrow_count = maxRs.getInt("borrow_count");
                        // System.out.println("borrow_count = "+ borrow_count);
                        System.out.println("category_name = " + category_name);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }

                // SQL查询语句，获取过去七天内每天借出图书的数量，如果没有借出，用零填充
                String dateSQL = "SELECT days.borrow_date AS borrow_date, IFNULL(COUNT(br.borrow_date), 0) AS total_books_borrowed FROM (SELECT CURDATE() - INTERVAL n DAY AS borrow_date FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) days) days LEFT JOIN borrow_record br ON DATE(br.borrow_date) = days.borrow_date GROUP BY days.borrow_date ORDER BY days.borrow_date;";
                Map<Date, Integer> borrowRecords = new HashMap<>();

                try (PreparedStatement dateStmt = conn.prepareStatement(dateSQL);
                     ResultSet dateRs = dateStmt.executeQuery()) {
                    // 执行查询并获取结果
                    while (dateRs.next()) {
                        Timestamp timestamp = dateRs.getTimestamp("borrow_date");
                        Date borrowDate = (timestamp != null) ? new Date(timestamp.getTime()) : null;
                        int totalBooksBorrowed = dateRs.getInt("total_books_borrowed");
                        borrowRecords.put(borrowDate, totalBooksBorrowed);
                    }
                } catch (SQLException e) {
                    log("Database error: ", e); // 使用Servlet的log方法记录错误
                    try (PrintWriter writer = response.getWriter()) {
                        response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR); // 设置HTTP状态码为500
                        writer.write("{\"error\":\"Database error occurred\"}"); // 返回错误信息的JSON响应
                    } catch (IOException ex) {
                        ex.printStackTrace();
                    }
                    return; // 结束方法执行
                }
            } catch (SQLException e) {
                // 处理数据库连接或查询错误
                log("Database error: ", e); // 使用Servlet的log方法记录错误
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR); // 设置HTTP状态码为500
                try (PrintWriter writer = response.getWriter()) {
                    writer.write("{\"error\":\"Database error occurred\"}"); // 返回错误信息的JSON响应
                }
                return; // 结束方法执行
            }
            // 将结果以JSON格式写入响应
            // 定义日期格式
            dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            try (PrintWriter writer = response.getWriter()) {
                String jsonResponse = "{" +
                        "\"todayBorrowCount\":" + todayBorrowCount + "," +
                        "\"todayReturnCount\":" + todayReturnCount + "," +
                        "\"userCount\":" + userCount + "," +
                        "\"category_name\":\"" + category_name + "\"," +
                        "\"borrow_count\":" + borrow_count + "," +
                        "\"noticeCount\":" + announcementCount + "," +
                        "\"date1\":\"" + dateFormat.format(date1) + "\"," +
                        "\"books1\":" + books1 + "," +
                        "\"date2\":\"" + dateFormat.format(date2) + "\"," +
                        "\"books2\":" + books2 + "," +
                        "\"date3\":\"" + dateFormat.format(date3) + "\"," +
                        "\"books3\":" + books3 + "," +
                        "\"date4\":\"" + dateFormat.format(date4) + "\"," +
                        "\"books4\":" + books4 + "," +
                        "\"date5\":\"" + dateFormat.format(date5) + "\"," +
                        "\"books5\":" + books5 + "," +
                        "\"date6\":\"" + dateFormat.format(date6) + "\"," +
                        "\"books6\":" + books6 + "," +
                        "\"date7\":\"" + dateFormat.format(date7) + "\"," +
                        "\"books7\":" + books7 + "}";
                writer.write(jsonResponse);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}